IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CloneQuestionnaireVersion]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CloneQuestionnaireVersion]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


/***************************************************************************
* Name:			CloneQuestionnaireVersion
* Purpose:		Clones an existing version. Called from SaveQuestionnaireVersion. 
			This procedure assumes that the QuestionnaireVersion record is created and
			starts cloning all its dependant records, records of the following tables

			QuestionnaireItem
			ItemOption
			ItemProperty
			QuestionDocument
			Script
			Script_QuestionnaireVersion_Map
*
* PARAMETERS
* Name			Description					
* -------------		-----------------------------------------------------------
* @newVersionID		id of the new version
* @oldVersionId		id of the previous version
***************************************************************************/

CREATE PROCEDURE [dbo].[CloneQuestionnaireVersion] (

@newVersionId INT,
@oldVersionId INT

)

AS

DECLARE @TempQuestionnaireItem TABLE (
		[ROWNUM] [int] IDENTITY(1, 1),
		[ID] [int] NOT NULL ,
		[VersionID] [int] NOT NULL ,
		[ItemName] [varchar] (30) NOT NULL ,
		[ItemText] [varchar] (4000) NULL ,
		[Required] [bit] NOT NULL Default (0) ,
		[ValidationMessage] [varchar] (250) NULL ,
		[ItemType] [nvarchar] (30) NOT NULL ,
		[HasSeparator] [bit] NOT NULL ,
		[Indent] [bit] NOT NULL Default (0),
		[ItemOrder] [int] NOT NULL ,
		[ParentName] [varchar] (30) NULL ,
		[ParentValue] [varchar] (30) NULL,
		[OptionsRefDomain] [Varchar] (50) NULL
	)

DECLARE @TempItemOption TABLE(
	[ROWNUM] [int] IDENTITY(1, 1),
	[ID] [int] NOT NULL,
	[ItemID] [int] NOT NULL,
	[OptionText] [nvarchar](1000) NOT NULL,
	[OptionValue] [nvarchar](30) NOT NULL
	)

DECLARE @TempItemProperty TABLE(
	[ROWNUM] [int] IDENTITY(1, 1),
	[ID] [int] NOT NULL,
	[ItemID] [int] NOT NULL,
	[Attribute] [nvarchar](20) NOT NULL,
	[AttributeValue] [nvarchar](100) NOT NULL
	)
	
DECLARE @TempQuestionDocument TABLE(
	[ROWNUM] [int] IDENTITY(1, 1),
	[ID] [int] NOT NULL,
	[VersionID] [int] NOT NULL,
	[Type] [varchar](50) NOT NULL,
	[Required] [bit] NOT NULL,
	[ItemName] [varchar](30) NOT NULL,
	[ItemValue] [varchar](30) NOT NULL,
	[ValidationMessage] [varchar](250) NULL
	)

DECLARE @TempScript TABLE (
	[ROWNUM] [int] IDENTITY(1, 1),
	[Id] [int] NOT NULL ,
	[Name] [varchar] (40)	NOT NULL ,
	[ScriptText] [Text]		NOT NULL ,
	[Type]  [varchar] (50)  NOT NULL 
	)
	
DECLARE @TempScript_QuestionnaireVersion_Map TABLE (
	[ROWNUM] [int] IDENTITY(1, 1),
	[ScriptId] [int]  NOT NULL ,
	[QuestionnaireVersionId] [int] NOT NULL	
	)
			
DECLARE @questionnaireItemRecordCount INT
DECLARE @itemIndex INT
DECLARE @index INT
DECLARE @oldItemId INT
DECLARE @newItemId INT
DECLARE @recordCount INT
DECLARE @scriptRecordCount INT
DECLARE @scriptIndex INT
DECLARE @newScriptId INT
BEGIN

	
	INSERT INTO @TempQuestionnaireItem (Id, VersionId, ItemName, ItemText, Required, ValidationMessage, ItemType, HasSeparator, Indent, ItemOrder, ParentName, ParentValue, OptionsRefDomain)
	SELECT Id, VersionId, ItemName, ItemText, Required, ValidationMessage, ItemType, HasSeparator, Indent, ItemOrder, ParentName, ParentValue, OptionsRefDomain
	FROM QuestionnaireItem
	WHERE versionId = @oldVersionId
	
	
	UPDATE @TempQuestionnaireItem
	SET VersionId = @newVersionId
	
	SELECT @questionnaireItemRecordCount = count(*) from @TempQuestionnaireItem
	

	SET @itemIndex = 1
	
	WHILE (@itemIndex <= @questionnaireItemRecordCount)
	BEGIN
		--print 'item'
		SELECT @oldItemId = [id] FROM @TempQuestionnaireItem WHERE RowNum=@itemIndex
	
		INSERT INTO QuestionnaireItem (VersionId, ItemName, ItemText, Required, ValidationMessage, ItemType, HasSeparator, Indent, ItemOrder, ParentName, ParentValue, OptionsRefDomain)
		SELECT VersionId, ItemName, ItemText, Required, ValidationMessage, ItemType, HasSeparator, Indent, ItemOrder, ParentName, ParentValue, OptionsRefDomain
		FROM @TempQuestionnaireItem
		WHERE RowNum = @itemIndex
		
		SELECT @newItemId = IDENT_CURRENT('QuestionnaireItem')
		
		--print 'ItemOption'
		
		--starting index for new rows about to be inserted to temp table
		SELECT @index = count(*) from @TempItemOption
		SET @index = @index + 1
		
		--insert new rows into temp table
		INSERT INTO @TempItemOption (Id, ItemId, OptionText, OptionValue)
		SELECT Id, ItemId, OptionText, OptionValue
		FROM ItemOption
		WHERE ItemId = @oldItemId
		
		UPDATE @TempItemOption SET ItemId = @newItemId
		
		SELECT @recordCount = count(*) from @TempItemOption
	
		--SET @index = 1
	
		WHILE (@index <= @recordCount)
		BEGIN
			INSERT INTO ItemOption (ItemId, OptionText, OptionValue)
			SELECT ItemId, OptionText, OptionValue
			FROM @TempItemOption
			WHERE RowNum = @index
			
			SET @index = @index + 1
		END
	
		--TRUNCATE TABLE @TempItemOption

		
		--print 'ItemProperty'
		
		--starting index for new rows about to be inserted to temp table
		SELECT @index = count(*) from @TempItemProperty
		SET @index = @index + 1
		
		--insert new rows into temp table
		INSERT INTO @TempItemProperty (Id, ItemId, Attribute, AttributeValue)
		SELECT Id, ItemId, Attribute, AttributeValue
		FROM ItemProperty
		WHERE ItemId = @oldItemId
		
		UPDATE @TempItemProperty SET ItemId = @newItemId
		
		SELECT @recordCount = count(*) from @TempItemProperty
	
		--SET @index = 1
	
		WHILE (@index <= @recordCount)
		BEGIN
			INSERT INTO ItemProperty (ItemId, Attribute, AttributeValue)
			SELECT ItemId, Attribute, AttributeValue
			FROM @TempItemProperty
			WHERE RowNum = @index

			SET @index = @index + 1
		END
		--TRUNCATE TABLE @TempItemProperty
		
		SET @itemIndex = @itemIndex + 1
	END

	--Question Document	
	--print 'QD'
	INSERT INTO @TempQuestionDocument ([Id], VersionId, Type, Required, ItemName, ItemValue, ValidationMessage)
	SELECT [Id], VersionId, Type, Required, ItemName, ItemValue, ValidationMessage
	FROM QuestionDocument
	WHERE VersionId = @oldVersionId
	
	UPDATE @TempQuestionDocument SET VersionId = @newVersionId
	
	SELECT @recordCount = count(*) from @TempQuestionDocument
	
	SET @index = 1

	WHILE (@index <= @recordCount)
	BEGIN
		INSERT INTO QuestionDocument (VersionId, Type, Required, ItemName, ItemValue, ValidationMessage)
		SELECT VersionId, Type, Required, ItemName, ItemValue, ValidationMessage
		FROM @TempQuestionDocument
		WHERE RowNum = @index

		SET @index = @index + 1
	END
	DELETE FROM @TempQuestionDocument
	
	--Script
	--print 'Script'
	INSERT INTO @TempScript ([Id], [Name], ScriptText, Type)
	SELECT [Id], [Name], ScriptText, Type
	FROM Script INNER JOIN Script_QuestionnaireVersion_Map AS Map ON Script.[Id] = Map.ScriptId
	WHERE Map.QuestionnaireVersionId = @oldVersionId
	
	SELECT @scriptRecordCount = count(*) from @TempScript
	
	SET @scriptIndex = 1

	WHILE (@scriptIndex <= @scriptRecordCount)
	BEGIN

		INSERT INTO Script ([Name], ScriptText, Type)
		SELECT [Name], ScriptText, Type
		FROM @TempScript
		WHERE RowNum = @scriptIndex

		SELECT @newScriptId = IDENT_CURRENT('Script')
		
		
		INSERT INTO Script_QuestionnaireVersion_Map (ScriptId, QuestionnaireVersionId)
		Values(@newScriptId, @newVersionId)


		SET @scriptIndex = @scriptIndex + 1
	END
	DELETE FROM @TempScript
	
	
	return
	ERROR_HANDLER:
		RAISERROR('Unable to clone QuestionnaireVersion', 16, 1)
		RETURN
END

GO
